package com.soloyogame.anitoys.util.excel;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import com.soloyogame.anitoys.util.EvalTool;
import com.soloyogame.anitoys.util.StringTool;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExcelTool {

    public static void makeExcel(ExcelModel excel) {
        WritableWorkbook book = null;
        OutputStream os = null;
        try {
            os = new FileOutputStream(excel.getFileName());
            book = Workbook.createWorkbook(os);
            Label templabel;
            List<Object> dataList = excel.getDatalist();
            Object dataObj;
            Object dataItem;
            String dataItemStr;
            WritableFont font = new WritableFont(WritableFont.createFont("kai"), 12, WritableFont.BOLD, false);
            WritableCellFormat format = new WritableCellFormat(font);
            WritableCellFormat titleFormat = new WritableCellFormat(font);
            titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
            WritableSheet sheet = book.createSheet(excel.getSheetName(), 0);
            //Label label = new Label(col, row, title); 
            //构造文档标题
            templabel = new Label(0, 0, excel.getFileTitle(), titleFormat);
            sheet.addCell(templabel);
            for (int i = 1; i < excel.getTitles().length; i++) {
                templabel = new Label(i, 0, null, format);
                sheet.addCell(templabel);
                if (excel.getWidths() != null) {
                    sheet.setColumnView(i, excel.getWidths()[i]);
                }
            }
            sheet.mergeCells(0, 0, excel.getTitles().length - 1, 0); //合并单元格

            //构造标题行
            for (int i = 0; i < excel.getTitles().length; i++) {
                templabel = new Label(i, 1, excel.getTitles()[i], format);
                sheet.addCell(templabel);
                if (excel.getWidths() != null) {
                    sheet.setColumnView(i, excel.getWidths()[i]);
                }
            }

            //构造数据行
            for (int j = 0; j < dataList.size(); j++) {
                for (int i = 0; i < excel.getFields().length; i++) {
                    dataObj = dataList.get(j);
                    dataItem = EvalTool.evalObject(dataObj, excel.getFields()[i]);
                    dataItemStr = StringTool.toString(dataItem, excel.getDateFormat());
                    templabel = new Label(i, j + 2, dataItemStr);
                    sheet.addCell(templabel);
                }
            }
            book.write();
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    public void exportExcelByTemplate(ExcelModel excel) {

    }

//    /**
//     * 现货出单导出方法
//     *
//     * @param list
//     */
//    public static HSSFWorkbook excelPort(List<OrderManager> list) {
//
////    	String[] excelHeader={"janCode","订单编号","买家会员名","买家留言","收货人姓名","收货地址","运送方式","联系电话","联系手机","订单备注","物流单号","物流公司","发货时间","仓库备注"};
//        String[] excelHeader = {"janCode", "订单编号", "买家会员名", "买家留言", "收货人姓名", "收货地址", "联系电话", "联系手机", "宝贝标题", "宝贝种类 ", "物流单号", "物流公司", "发货时间", "仓库备注", "订单备注", "宝贝总数量", "购买数量"};
//        HSSFWorkbook wb = new HSSFWorkbook();
//        excelPortSheet1(list, wb);
//        HSSFSheet sheet = wb.createSheet("总单");
//        HSSFRow row = sheet.createRow((int) 0);
//        HSSFCellStyle style = wb.createCellStyle();
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//        for (short i = 0; i < excelHeader.length; i++) {
//            HSSFCell cell = row.createCell(i);
//            cell.setCellValue(excelHeader[i]);
//            cell.setCellStyle(style);
////    	 sheet.autoSizeColumn(i);
//            sheet.setColumnWidth(i, 3000);
//        }
//        for (int i = 0; i < list.size(); i++) {
//            row = sheet.createRow(i + 1);
//            OrderManager orderManager = list.get(i);
//            row.createCell(0).setCellValue(orderManager.getProductId());
//            row.createCell(1).setCellValue(orderManager.getId());
//            row.createCell(2).setCellValue(orderManager.getNickName());
//            row.createCell(3).setCellValue(orderManager.getBuyerMessage());
//            row.createCell(4).setCellValue(orderManager.getShipName());
//            row.createCell(5).setCellValue(orderManager.getShipAddress());
////	    	 row.createCell(6).setCellValue(orderManager.getCarry()); 
//            row.createCell(6).setCellValue(orderManager.getTel());
//            row.createCell(7).setCellValue(orderManager.getPhone());
//            row.createCell(8).setCellValue(orderManager.getProductName());
//            row.createCell(9).setCellValue(orderManager.getFirstType());
//            row.createCell(10).setCellValue(orderManager.getExpressNo());
//            row.createCell(11).setCellValue(orderManager.getExpressCompanyName());
//            row.createCell(12).setCellValue(orderManager.getDeliverTime());
//            row.createCell(13).setCellValue(orderManager.getWareHouseRemark());
//            row.createCell(14).setCellValue(orderManager.getRemark());
//            row.createCell(15).setCellValue(orderManager.getQuantity());
//            row.createCell(16).setCellValue(orderManager.getQuantity());
//        }
//        return wb;
//    }
//
//    public static void excelPortSheet1(List<OrderManager> list, HSSFWorkbook wb) {
//        String[] excelHeader = {"订单编号", "janCode11111111", "总计", "买家会员名", "买家留言", "收货人姓名", "收货地址", "运送方式", "联系电话", "联系手机", "订单备注", "物流单号", "物流公司", "发货时间", "仓库备注"};
//        HSSFSheet sheet = wb.createSheet("合并后订单");
//        HSSFRow row = sheet.createRow((int) 0);
//        HSSFCellStyle style = wb.createCellStyle();
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//        for (short i = 0; i < excelHeader.length; i++) {
//            HSSFCell cell = row.createCell(i);
//            cell.setCellValue(excelHeader[i]);
//            cell.setCellStyle(style);
////    	 sheet.autoSizeColumn(i);
//            sheet.setColumnWidth(i, 3000);
//        }
//        for (int i = 0; i < list.size(); i++) {
//            row = sheet.createRow(i + 1);
//            OrderManager orderManager = list.get(i);
//            row.createCell(0).setCellValue(orderManager.getId());
//            row.createCell(1).setCellValue(orderManager.getProductId());
//            row.createCell(2).setCellValue(orderManager.getQuantity());
//            row.createCell(3).setCellValue(orderManager.getNickName());
//            row.createCell(4).setCellValue(orderManager.getBuyerMessage());
//            row.createCell(5).setCellValue(orderManager.getShipName());
//            row.createCell(6).setCellValue(orderManager.getShipAddress());
//            row.createCell(7).setCellValue(orderManager.getShipAddress());
//            row.createCell(8).setCellValue(orderManager.getTel());
//            row.createCell(9).setCellValue(orderManager.getPhone());
//            row.createCell(10).setCellValue(orderManager.getRemark());
//            row.createCell(11).setCellValue(orderManager.getExpressNo());
//            row.createCell(12).setCellValue(orderManager.getExpressCompanyName());
//            row.createCell(13).setCellValue(orderManager.getDeliverTime());
//            row.createCell(14).setCellValue(orderManager.getWareHouseRemark());
//        }
//    }
//
//    /**
//     * 现货出单导出方法
//     *
//     * @param list
//     */
//    public static HSSFWorkbook excelPorts(List<OrderWork> list) {
//        String[] excelHeader = {"订单号", "商品janCode", "商品名", "商品单价", "购买数量", "购买人ID", "收货地址", "联系方式", "收货人姓名", "快递方式", "快递单号", "下单时间", "预定时间", "补款时间", "完成付款时间", "是否售后", "订单状态", "订单总额", "运费金额", "买家积分使用", "买家优惠券抵扣", "买家平台抵扣券的抵扣", "买家站内余额使用", "买家现金支付金额", "单品总额"};
//        HSSFWorkbook wb = new HSSFWorkbook();
//        HSSFSheet sheet = wb.createSheet("订单总表");
//        HSSFRow row0 = sheet.createRow((int) 0);
//
//        HSSFRow row = sheet.createRow((int) 1);
//        HSSFCellStyle style = wb.createCellStyle();
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
////    	for (short i = 0; i < excelHeader.length; i++) {
////    		HSSFCell cell = row0.createCell(i);
////    		cell.setCellValue(excelHeader[i]);
////    		cell.setCellStyle(style);  
//////    	 sheet.autoSizeColumn(i);
////    	 sheet.setColumnWidth(i, 3500);
////    	 }
//        // 单元格合并   四个参数分别是：起始行，起始列，结束行，结束列  
////    	sheet.addMergedRegion(new Region((short)0, (short)0, (short)0, (short)4));
////    	sheet.addMergedRegion(new Region((short)0, (short)5, (short)0, (short)10));
////    	sheet.addMergedRegion(new Region((short)0, (short)11, (short)0, (short)16));
////    	sheet.addMergedRegion(new Region((short)0, (short)17, (short)0, (short)23));
//        for (short i = 0; i < excelHeader.length; i++) {
//            HSSFCell cell = row.createCell(i);
//            cell.setCellValue(excelHeader[i]);
//            cell.setCellStyle(style);
////    	 sheet.autoSizeColumn(i);
//            sheet.setColumnWidth(i, 3500);
//        }
//        for (int i = 0; i < list.size(); i++) {
//            row = sheet.createRow(i + 2);
//            OrderWork orderWork = list.get(i);
//            row.createCell(0).setCellValue(orderWork.getId());
//            row.createCell(1).setCellValue(orderWork.getProductId());
//            row.createCell(2).setCellValue(orderWork.getProductName());
//            row.createCell(3).setCellValue(orderWork.getProductPrice());
//            row.createCell(4).setCellValue(orderWork.getNumber());
//            row.createCell(5).setCellValue(orderWork.getAccount());
//            row.createCell(6).setCellValue(orderWork.getShipAddress());
//            row.createCell(7).setCellValue(orderWork.getPhone());
//            row.createCell(8).setCellValue(orderWork.getShipName());
//            row.createCell(9).setCellValue(orderWork.getCarry());
//            row.createCell(10).setCellValue(orderWork.getExpressNo());
//            row.createCell(11).setCellValue(orderWork.getDownOrderTime());
//            row.createCell(12).setCellValue(orderWork.getBespeakTime());
//            row.createCell(13).setCellValue(orderWork.getReplenishmentTime());
//            row.createCell(14).setCellValue(orderWork.getFinishreplenishmentTime());
//            row.createCell(15).setCellValue(("1".equals(orderWork.getIsCustomer())) ? "是" : "否");
//            if (!StringUtils.isBlank(orderWork.getOrderStatus())) {
//                if (Order.order_status_init.equals(orderWork.getOrderStatus())) {
//                    row.createCell(16).setCellValue("待付款");
//                } else if (Order.order_status_pass.equals(orderWork.getOrderStatus())) {
//                    row.createCell(16).setCellValue("待补款");
//                } else if (Order.order_status_stay.equals(orderWork.getOrderStatus())) {
//                    row.createCell(16).setCellValue("待发货");
//                } else if (Order.order_status_send.equals(orderWork.getOrderStatus())) {
//                    row.createCell(16).setCellValue("待收货");
//                } else if (Order.order_status_sign.equals(orderWork.getOrderStatus())) {
//                    row.createCell(16).setCellValue("已完成");
//                } else if (Order.order_status_none.equals(orderWork.getOrderStatus())) {
//                    row.createCell(16).setCellValue("已取消");
//                } else if (Order.order_status_cancel.equals(orderWork.getOrderStatus())) {
//                    row.createCell(16).setCellValue("已取消");
//                } else if (Order.order_status_file.equals(orderWork.getOrderStatus())) {
//                    row.createCell(16).setCellValue("已归档");
//                } else {
//                    row.createCell(16).setCellValue("");
//                }
//            } else {
//                row.createCell(16).setCellValue("");
//            }
//            row.createCell(17).setCellValue(orderWork.getOrderSumPrice());
//            row.createCell(18).setCellValue(orderWork.getFeeSumPrice());
//            row.createCell(19).setCellValue(orderWork.getBuyerRank());
//            row.createCell(20).setCellValue(orderWork.getBuyerCoupon());
//            row.createCell(21).setCellValue(orderWork.getBuyerCouponPlat());
//            row.createCell(22).setCellValue(orderWork.getBuyerAmount());
//            row.createCell(23).setCellValue(orderWork.getBuyerPayMoney());
//            row.createCell(24).setCellValue(orderWork.getPtotal());
//        }
//        return wb;
//    }

    public static void main(String[] args) {
        try {
            //com.ehero.test.DbTest db=new com.ehero.test.DbTest();
            List dataList = new ArrayList();
            //=db.getFunctionList("");

            ExcelModel excel = new ExcelModel();
            excel.setFileName("d://dev//demoExcel.xls");
            excel.setSheetName("2010-01-01到2010-02-01");
            excel.setFileTitle("查询列表");
            excel.setDateFormat("yyyy-MM-dd");
            String[] titles = {"菜单ID", "名称", "url"};
            String[] fields = {"item.function_id", "item.name_", "item.url_"};
            int[] widths = {10, 20, 20, 30};
            excel.setTitles(titles);
            excel.setFields(fields);
            excel.setWidths(widths);
            excel.setDatalist(dataList);
            new ExcelTool().makeExcel(excel);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

}
